package schoolsource.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;

import schoolsource.model.Item;
import schoolsource.model.PasswordItem;
import schoolsource.util.SchoolLogger;

/**
 * 
 * @author Steven Steele
 * Copyright 2003
 *
 * Contains methods for working with logins/passwords.
 */
public class LoginSql {

//	private static final String dataSource = "jdbc:caucho:school2";

	/**
	 * Verifies that the provided username and password are valid and returns the number
	 * of consecutive failed login attempts. 
	 * @param username The username of the user.
	 * @param password The password of the user.
	 * @param failedlogins An 'empty' container to store the number of failed logins and that
	 * provides the column name where failed logins occur.
	 * @return The failed logins item.
	 * TODO This both alters the passed-in item an returns the item that was passed in - fix this
	 */
	public static Item verify(Item username, PasswordItem password, Item failedlogins, String dataSource) {
		Connection connection = null;
		ResultSet results = null;
		try {
			connection = DriverManager.getConnection(dataSource);
			Statement statement = connection.createStatement();
			MySQLAccessor msqla = new MySQLAccessor(dataSource, "student1");

			String queryString =
				"SELECT * FROM student WHERE "
					+ username.getFieldName()
					+ " = "
					+ username.getDBFormattedString(msqla)
					+ " AND "
					+ password.getFieldName()
					+ " = "
					+ password.getDBFormattedString(msqla)
					+ ");";
			results = statement.executeQuery(queryString);
			if (results != null && results.next()) {
				failedlogins.setValue(
					results.getString(failedlogins.getFieldName()));
			}
		} catch (SQLException sqle) {
			throw new RuntimeException(
				sqle.toString() + " Couldn't write to the database.");
		} finally {
			try {
				connection.close();
			} catch (SQLException sqle) {
				SchoolLogger.logger.log(
					Level.WARNING,
					"LoginSql.verify(String, String): Couldn't close the database.");
			}
		}

		return failedlogins;
	}
}